﻿using DocumentFormat.OpenXml.Office2016.Drawing.ChartDrawing;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.Extensions.Logging;
using NP.BPMReportPlatform.AuthorizationProcessesManager.Dtos;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.RecurringJobs.Dto.YCLWLJGZSYC;
using NP.BPMReportPlatform.Utilities;
using NP.BPMReportPlatform.Utils;
using NP.BPMReportPlatform.YCLWLJGZSYC.Dto;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Crypto.Tls;
using Serilog;
using Serilog.Core;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Validation.Localization;
using ILogger = Serilog.ILogger;
using Tuple = System.Tuple;

namespace NP.BPMReportPlatform.YCLWLJGZSYC
{
    public class YCLWLJGZSYCService : BPMReportPlatformAppService, IYCLWLJGZSYCService
    {
        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;
        private readonly ILogger _logger;

        public YCLWLJGZSYCService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp, ILogger logger)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
            _logger = logger;
        }

        public async Task<PagedResultDto<YCLWLJGZSYCOutput>> GetPageList(YCLWLJGZSYCInput input)
        {
            RefAsync<int> totalCount = 0;
            var query = GetQueryable(input);
            var resItems = await query.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            resItems.ForEach(x =>
            {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
            });
            return new PagedResultDto<YCLWLJGZSYCOutput>
            {
                TotalCount = totalCount,
                Items = resItems,
            };
        }

        public async Task<IWorkbook> ExportData(YCLWLJGZSYCInput input)
        {
            var dataList = await GetQueryable(input, true).ToListAsync();

            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("Sheet1");
            sheet.DisplayGridlines = true;
            IRow row = sheet.CreateRow(0); // 添加表头
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.Alignment = HorizontalAlignment.Center;
            style1.BorderBottom = BorderStyle.Thin;
            style1.BorderLeft = BorderStyle.Thin;
            style1.BorderRight = BorderStyle.Thin;
            style1.BorderTop = BorderStyle.Thin;

            var headerColumns = InitHeaderColumns();
            for (var i = 0; i < headerColumns.Count; i++)
            {
                sheet.SetColumnWidth(i, 15 * 256);
                row.CreateCell(i).SetCellValue(headerColumns[i]);
                row.GetCell(i).CellStyle = style1;
            }
            var headerDataList = dataList.GroupBy(x => x.InstanceNumber).ToList();
            var headerIndex = 1;
            var detailIndex = 0;
            foreach(var headerData in headerDataList)
            {
                var header = headerData.FirstOrDefault();
                if (header == null)
                {
                    continue;
                }

                IRow rowtemp = sheet.CreateRow(headerIndex + detailIndex);
                rowtemp.CreateCell(0).SetCellValue(headerIndex);
                rowtemp.CreateCell(1).SetCellValue(header.InstanceNumber);
                rowtemp.CreateCell(2).SetCellValue(header.AddUserName);
                rowtemp.CreateCell(3).SetCellValue(header.AddTimeString);
                rowtemp.CreateCell(4).SetCellValue(header.MaterialType == "2" ? "包材" : "原材料");
                rowtemp.CreateCell(5).SetCellValue(GetMaintenanceTypeName(header.MaintenanceType));
                rowtemp.CreateCell(6).SetCellValue(header.DEPT);
                rowtemp.CreateCell(7).SetCellValue(header.StatusName);
                rowtemp.CreateCell(8).SetCellValue(header.AuditPerson);
                rowtemp.CreateCell(9).SetCellValue(header.Remark);
                for (var k = 0; k <= 8; k++)
                {
                    rowtemp.GetCell(k).CellStyle = style1;
                }
                headerIndex++;

                var columns = InitDetailColumns(); // 子表表头
                if (header.MaintenanceType == (int)TimeTypeEnum.ThreeYearOnSeason)
                {
                    var timeTypeDatas = await _db.Queryable<YCLWLJGZSYCTimeType>().Where(x => x.InstanceNumber == header.InstanceNumber).ToListAsync();
                    var yearTimeTypeData = timeTypeDatas.FirstOrDefault(x => x.MaintenanceType == (int)TimeTypeEnum.YearOnMonth);
                    var nextYearFirstMonthStr = header.CurrentTime;
                    var dataIndex = 1;
                    if (yearTimeTypeData != null)
                    {
                        nextYearFirstMonthStr = GetNextYearFirstSeasonTimeStr(yearTimeTypeData.CurrentTime);
                        var monthNum = GetCurrentYearLeftMonth(yearTimeTypeData.CurrentTime);
                        columns.Add(new DetailColumn
                        {
                            field = "current_num",
                            title = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime).Item2,
                            timeid = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime).Item1,
                        });
                        for (var i = 1; i <= monthNum; i++)
                        {
                            dataIndex++;
                            columns.Add(new DetailColumn
                            {
                                field = $"num{i}",
                                title = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime, i).Item2,
                                timeid = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime, i).Item1,
                            });
                        }
                    }

                    var threeYearTimeTypeData = timeTypeDatas.FirstOrDefault(x => x.MaintenanceType == (int)TimeTypeEnum.ThreeYearOnSeason);
                    if (threeYearTimeTypeData != null)
                    {
                        if (columns.Any(x => x.field == "current_num"))
                        {
                            for (var i = 0; i <= 23; i++)
                            {
                                columns.Add(new DetailColumn
                                {
                                    field = $"num{dataIndex + i}",
                                    title = GetCurrentNextTimeForTimeStr(threeYearTimeTypeData.CurrentTime, i).Item2,
                                    timeid = GetCurrentNextTimeForTimeStr(threeYearTimeTypeData.CurrentTime, i).Item1,
                                });
                            }
                        }
                        else
                        {
                            columns.Add(new DetailColumn
                            {
                                field = "current_num",
                                title = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr).Item2,
                                timeid = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr).Item1,
                            });
                            for (var i = 1; i <= 23; i++)
                            {
                                columns.Add(new DetailColumn
                                {
                                    field = $"num{i}",
                                    title = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr, i).Item2,
                                    timeid = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr, i).Item1,
                                });
                            }
                        }
                    }
                }
                else
                {
                    columns.Add(new DetailColumn
                    {
                        field = "current_num",
                        title = GetCurrentNextTimeForTimeStr(header.CurrentTime).Item2,
                        timeid = GetCurrentNextTimeForTimeStr(header.CurrentTime).Item1,
                    });
                    for (var i = 1; i <= 23; i++)
                    {
                        columns.Add(new DetailColumn
                        {
                            field = $"num{i}",
                            title = GetCurrentNextTimeForTimeStr(header.CurrentTime, i).Item2,
                            timeid = GetCurrentNextTimeForTimeStr(header.CurrentTime, i).Item1,
                        });
                    }
                }

                IRow subthtemp = sheet.CreateRow(headerIndex + detailIndex);
                for (var i = 0; i < columns.Count; i++)
                {
                    subthtemp.CreateCell(i).SetCellValue(columns[i].title);
                    subthtemp.GetCell(i).CellStyle = style1;
                }
                detailIndex++;

                var detailDataList = headerData.GroupBy(x =>
                    new { x.DEPT, x.CATEGORY_ID, x.SUB_CATEGORY_ID, x.CHILD_CATEGORY_ID, x.CATEGORY_MANAGER_CODE })
                    .OrderBy(x => x.First().DEPT).ThenBy(x => x.First().CATEGORY_ID)
                    .ThenBy(x => x.First().SUB_CATEGORY_ID).ThenBy(x => x.First().CHILD_CATEGORY_ID).ToList();
                var detailDatas = new List<dynamic>(); // 子表数据

                var detailDataColumnIndex = 1;
                foreach (var detail in detailDataList)
                {
                    IRow subrowtemp = sheet.CreateRow(headerIndex + detailIndex);
                    subrowtemp.CreateCell(0).SetCellValue(detailDataColumnIndex);
                    subrowtemp.CreateCell(1).SetCellValue(detail.FirstOrDefault()?.DEPT);
                    subrowtemp.CreateCell(2).SetCellValue(detail.FirstOrDefault()?.CATEGORY);
                    subrowtemp.CreateCell(3).SetCellValue(detail.FirstOrDefault()?.SUB_CATEGORY);
                    subrowtemp.CreateCell(4).SetCellValue(detail.FirstOrDefault()?.CHILD_CATEGORY);
                    subrowtemp.CreateCell(5).SetCellValue(detail.FirstOrDefault()?.CATEGORY_MANAGER_CODE);
                    subrowtemp.CreateCell(6).SetCellValue(detail.FirstOrDefault()?.CATEGORY_MANAGER);
                    subrowtemp.CreateCell(7).SetCellValue(detail.FirstOrDefault()?.GMV?.ToString());
                    subrowtemp.CreateCell(8).SetCellValue(detail.FirstOrDefault()?.CATEGORY_RATIO?.ToString());
                    subrowtemp.CreateCell(9).SetCellValue(detail.FirstOrDefault()?.TOTAL_RATIO?.ToString());
                    subrowtemp.CreateCell(10).SetCellValue(detail.FirstOrDefault()?.pre_num3?.ToString());
                    subrowtemp.CreateCell(11).SetCellValue(detail.FirstOrDefault()?.pre_num2?.ToString());
                    subrowtemp.CreateCell(12).SetCellValue(detail.FirstOrDefault()?.pre_num1?.ToString());

                    int j = 0;
                    foreach (var col in columns)
                    {
                        if (!string.IsNullOrEmpty(col.timeid))
                        {
                            var ratio = detail.FirstOrDefault(x => x.MONTH_ID == col.timeid)?.RATIO;
                            subrowtemp.CreateCell(j).SetCellValue(ratio?.ToString());
                        }
                        j++;
                    }
                    detailIndex++;
                    detailDataColumnIndex++;
                }
            }
            return workbook;
        }

        private string GetMaintenanceTypeName(int maintenanceType)
        {
            if (maintenanceType == 1)
            {
                return "按月维护";
            }
            if (maintenanceType == 2)
            {
                return "按季度维护";
            }
            if (maintenanceType == 3)
            {
                return "按半年维护";
            }
            if (maintenanceType == 4)
            {
                return "按半年维护";
            }
            if (maintenanceType == 5)
            {
                return "按一年维护";
            }
            if (maintenanceType == 6)
            {
                return "按三年维护";
            }
            return "";
        }

        public ISugarQueryable<YCLWLJGZSYCOutput> GetQueryable(YCLWLJGZSYCInput input, bool isExport = false)
        {
            if (isExport)
            {
                var query = _db.Queryable<YCLWLJGZSYCHeader, YCLWLJGZSYCChildData, BusinessProcessesNew>(
                    (h, c, b) => new JoinQueryInfos(JoinType.Inner, h.InstanceNumber == c.InstanceNumber,
                    JoinType.Inner, h.InstanceNumber == b.InstanceId && b.BTID == "YCLWLJGZSYC"))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (h, c, b) => h.InstanceNumber.Contains(input.InstanceNumber))
                    .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (h, c, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusCode))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), (h, c, b) => h.AddUserName.Contains(input.AddUserName))
                    .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, (h, c, b) => h.AddTime >= input.StartCreationTime && h.AddTime <= input.EndCreationTime)
                    .WhereIF(input.MaintenanceType > 0, (h, c, b) => h.MaintenanceType == input.MaintenanceType)
                    .WhereIF(!string.IsNullOrEmpty(input.Dept), (h, c, b) => c.DEPT == input.Dept)
                    .WhereIF(!string.IsNullOrEmpty(input.MaterialType), (h, c, b) => h.MaterialType == input.MaterialType || SqlFunc.IsNullOrEmpty(h.MaterialType) && input.MaterialType == "1")
                    .Select((h, c, b) => new YCLWLJGZSYCOutput
                    {
                        InstanceNumber = h.InstanceNumber,
                        AddTime = h.AddTime,
                        AddTimeString = h.AddTime.ToString(DateFormatHelper.DateTimeFormat),
                        AddUserName = h.AddUserName,
                        StatusName = b.ProcessStatusName,
                        MaintenanceType = h.MaintenanceType,
                        MaterialType = h.MaterialType,
                        AuditPerson = h.CurrentOperateUserName,
                        CurrentTime = h.CurrentTime,
                        MONTH_ID = c.MONTH_ID,
                        DEPT = c.DEPT,
                        CATEGORY_ID = c.CATEGORY_ID,
                        CATEGORY = c.CATEGORY,
                        SUB_CATEGORY_ID = c.SUB_CATEGORY_ID,
                        SUB_CATEGORY = c.SUB_CATEGORY,
                        CHILD_CATEGORY_ID = c.CHILD_CATEGORY_ID,
                        CHILD_CATEGORY = c.CHILD_CATEGORY,
                        CATEGORY_MANAGER_CODE = c.CATEGORY_MANAGER_CODE,
                        CATEGORY_MANAGER = c.CATEGORY_MANAGER,
                        GMV = c.GMV,
                        CATEGORY_RATIO = c.CATEGORY_RATIO,
                        TOTAL_RATIO = c.TOTAL_RATIO,
                        pre_num3 = c.pre_num3,
                        pre_num2 = c.pre_num2,
                        pre_num1 = c.pre_num1,
                        RATIO = c.RATIO,
                        Remark = h.Remark,
                    });

                return query.MergeTable().OrderBy(x => x.AddTime, OrderByType.Desc);
            }
            else
            {
                var query = _db.Queryable<YCLWLJGZSYCHeader, YCLWLJGZSYCChildData, BusinessProcessesNew>(
                    (h, d, b) => new JoinQueryInfos(JoinType.Inner, h.InstanceNumber == d.InstanceNumber,
                    JoinType.Inner, h.InstanceNumber == b.InstanceId && b.BTID == "YCLWLJGZSYC"))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (h, d, b) => h.InstanceNumber.Contains(input.InstanceNumber))
                    .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (h, d, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusCode))
                    .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), (h, d, b) => h.AddUserName.Contains(input.AddUserName))
                    .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, (h, d, b) => h.AddTime >= input.StartCreationTime && h.AddTime <= input.EndCreationTime)
                    .WhereIF(input.MaintenanceType > 0, (h, d, b) => h.MaintenanceType == input.MaintenanceType)
                    .WhereIF(!string.IsNullOrEmpty(input.Dept), (h, d, b) => d.DEPT == input.Dept)
                    .WhereIF(!string.IsNullOrEmpty(input.MaterialType), (h, d, b) => h.MaterialType == input.MaterialType || SqlFunc.IsNullOrEmpty(h.MaterialType) && input.MaterialType == "1")
                    .Select((h, d, b) => new YCLWLJGZSYCOutput
                    {
                        InstanceNumber = h.InstanceNumber,
                        AddTime = h.AddTime,
                        AddTimeString = h.AddTime.ToString(DateFormatHelper.DateTimeFormat),
                        AddUserName = h.AddUserName,
                        StatusName = b.ProcessStatusName,
                        MaintenanceType = h.MaintenanceType,
                        DEPT = d.DEPT,
                        AuditPerson = h.CurrentOperateUserName,
                        MaterialType = h.MaterialType,
                    });

                return query.MergeTable().GroupBy(x => x.InstanceNumber).OrderBy(x => x.AddTime, OrderByType.Desc);
            }
        }

        public async Task<YCLWLJGZSYCDetailData> GetDetailData(string instanceNumber)
        {
            var res = new YCLWLJGZSYCDetailData();
            var header = await _db.Queryable<YCLWLJGZSYCHeader>().FirstAsync(x => x.InstanceNumber == instanceNumber);
            if (header == null)
            {
                throw new Exception("数据未找到");
            }
            var detailList = await _db.Queryable<YCLWLJGZSYCDetail>().Where(x => x.InstanceNumber == instanceNumber).ToListAsync();
            var childDataList = await _db.Queryable<YCLWLJGZSYCChildData>().Where(x => x.InstanceNumber == instanceNumber).ToListAsync();
            var columns = InitDetailColumns();            
            if (header.MaintenanceType == (int)TimeTypeEnum.ThreeYearOnSeason)
            {
                var timeTypeDatas = await _db.Queryable<YCLWLJGZSYCTimeType>().Where(x => x.InstanceNumber == instanceNumber).ToListAsync();
                var yearTimeTypeData = timeTypeDatas.FirstOrDefault(x => x.MaintenanceType == (int)TimeTypeEnum.YearOnMonth);
                var nextYearFirstMonthStr = header.CurrentTime;
                var dataIndex = 1;
                if (yearTimeTypeData != null)
                {
                    nextYearFirstMonthStr = GetNextYearFirstSeasonTimeStr(yearTimeTypeData.CurrentTime);
                    var monthNum = GetCurrentYearLeftMonth(yearTimeTypeData.CurrentTime);
                    columns.Add(new DetailColumn
                    {
                        field = "current_num",
                        title = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime).Item2,
                        timeid = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime).Item1,
                    });
                    for (var i = 1; i <= monthNum; i++)
                    {
                        dataIndex++;
                        columns.Add(new DetailColumn
                        {
                            field = $"num{i}",
                            title = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime, i).Item2,
                            timeid = GetCurrentNextTimeForTimeStr(yearTimeTypeData.CurrentTime, i).Item1,
                        });
                    }
                }

                var threeYearTimeTypeData = timeTypeDatas.FirstOrDefault(x => x.MaintenanceType == (int)TimeTypeEnum.ThreeYearOnSeason);
                if (threeYearTimeTypeData != null)
                {
                    if (columns.Any(x => x.field == "current_num"))
                    {
                        for (var i = 0; i <= 23; i++)
                        {
                            columns.Add(new DetailColumn
                            {
                                field = $"num{dataIndex + i}",
                                title = GetCurrentNextTimeForTimeStr(threeYearTimeTypeData.CurrentTime, i).Item2,
                                timeid = GetCurrentNextTimeForTimeStr(threeYearTimeTypeData.CurrentTime, i).Item1,
                            });
                        }
                    }
                    else
                    {
                        columns.Add(new DetailColumn
                        {
                            field = "current_num",
                            title = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr).Item2,
                            timeid = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr).Item1,
                        });
                        for (var i = 1; i <= 23; i++)
                        {
                            columns.Add(new DetailColumn
                            {
                                field = $"num{i}",
                                title = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr, i).Item2,
                                timeid = GetCurrentNextTimeForTimeStr(nextYearFirstMonthStr, i).Item1,
                            });
                        }
                    }
                }
            }
            else
            {
                columns.Add(new DetailColumn
                {
                    field = "current_num",
                    title = GetCurrentNextTimeForTimeStr(header.CurrentTime).Item2,
                    timeid = GetCurrentNextTimeForTimeStr(header.CurrentTime).Item1,
                });
                for (var i = 1; i <= 24; i++)
                {
                    columns.Add(new DetailColumn
                    {
                        field = $"num{i}",
                        title = GetCurrentNextTimeForTimeStr(header.CurrentTime, i).Item2,
                        timeid = GetCurrentNextTimeForTimeStr(header.CurrentTime, i).Item1,
                    });
                }
            }
            res.DetailColumns = columns;

            detailList = detailList.GroupBy(x => 
                new { x.DEPT, x.CATEGORY_ID, x.SUB_CATEGORY_ID, x.CHILD_CATEGORY_ID, x.CATEGORY_MANAGER_CODE })
                .Select(x => x.FirstOrDefault()).OrderBy(x => x.rownum).ToList();
            foreach (var detail in detailList)
            {
                var childData = childDataList.Where(x =>
                    x.DEPT == detail.DEPT && x.CATEGORY_ID == detail.CATEGORY_ID &&
                    x.SUB_CATEGORY_ID == detail.SUB_CATEGORY_ID &&
                    x.CHILD_CATEGORY_ID == detail.CHILD_CATEGORY_ID &&
                    x.CATEGORY_MANAGER_CODE == detail.CATEGORY_MANAGER_CODE).ToList();

                var detailData = GetRowData(res.DetailColumns, detail, childData);
                res.DetailDatas.Add(detailData);
            }
            return res;
        }

        private dynamic GetRowData(List<DetailColumn> DetailColumns, YCLWLJGZSYCDetail detail, List<YCLWLJGZSYCChildData> childDatas)
        {
            dynamic item = new ExpandoObject();
            item.DEPT = detail.DEPT;
            item.CATEGORY = detail.CATEGORY;
            item.SUB_CATEGORY = detail.SUB_CATEGORY;
            item.CHILD_CATEGORY = detail.CHILD_CATEGORY;
            item.CATEGORY_MANAGER_CODE = detail.CATEGORY_MANAGER_CODE;
            item.CATEGORY_MANAGER = detail.CATEGORY_MANAGER;
            item.TOTAL_RATIO = detail.TOTAL_RATIO;
            item.GMV = detail.GMV;
            item.CATEGORY_RATIO = detail.CATEGORY_RATIO;
            item.pre_num3 = detail.pre_num3;
            item.pre_num2 = detail.pre_num2;
            item.pre_num1 = detail.pre_num1;

            foreach (var col in DetailColumns)
            {
                if (!string.IsNullOrEmpty(col.timeid))
                {
                    var ratio = childDatas.FirstOrDefault(x => x.MONTH_ID == col.timeid)?.RATIO;
                    if (col.field == "current_num")
                    {
                        item.current_num = ratio;
                    }
                    else if (col.field == "num1")
                    {
                        item.num1 = ratio;
                    }
                    else if (col.field == "num2")
                    {
                        item.num2 = ratio;
                    }
                    else if (col.field == "num3")
                    {
                        item.num3 = ratio;
                    }
                    else if (col.field == "num4")
                    {
                        item.num4 = ratio;
                    }
                    else if (col.field == "num5")
                    {
                        item.num5 = ratio;
                    }
                    else if (col.field == "num6")
                    {
                        item.num6 = ratio;
                    }
                    else if (col.field == "num7")
                    {
                        item.num7 = ratio;
                    }
                    else if (col.field == "num8")
                    {
                        item.num8 = ratio;
                    }
                    else if (col.field == "num9")
                    {
                        item.num9 = ratio;
                    }
                    else if (col.field == "num10")
                    {
                        item.num10 = ratio;
                    }
                    else if (col.field == "num11")
                    {
                        item.num11 = ratio;
                    }
                    else if (col.field == "num12")
                    {
                        item.num12 = ratio;
                    }
                    else if (col.field == "num13")
                    {
                        item.num13 = ratio;
                    }
                    else if (col.field == "num14")
                    {
                        item.num14 = ratio;
                    }
                    else if (col.field == "num15")
                    {
                        item.num15 = ratio;
                    }
                    else if (col.field == "num16")
                    {
                        item.num16 = ratio;
                    }
                    else if (col.field == "num17")
                    {
                        item.num17 = ratio;
                    }
                    else if (col.field == "num18")
                    {
                        item.num18 = ratio;
                    }
                    else if (col.field == "num19")
                    {
                        item.num19 = ratio;
                    }
                    else if (col.field == "num20")
                    {
                        item.num20 = ratio;
                    }
                    else if (col.field == "num21")
                    {
                        item.num21 = ratio;
                    }
                    else if (col.field == "num22")
                    {
                        item.num22 = ratio;
                    }
                    else if (col.field == "num23")
                    {
                        item.num23 = ratio;
                    }
                    else if (col.field == "num24")
                    {
                        item.num24 = ratio;
                    }
                    else if (col.field == "num25")
                    {
                        item.num25 = ratio;
                    }
                    else if (col.field == "num26")
                    {
                        item.num26 = ratio;
                    }
                    else if (col.field == "num27")
                    {
                        item.num27 = ratio;
                    }
                    else if (col.field == "num28")
                    {
                        item.num28 = ratio;
                    }
                    else if (col.field == "num29")
                    {
                        item.num29 = ratio;
                    }
                    else if (col.field == "num30")
                    {
                        item.num30 = ratio;
                    }
                    else if (col.field == "num31")
                    {
                        item.num31 = ratio;
                    }
                    else if (col.field == "num32")
                    {
                        item.num32 = ratio;
                    }
                    else if (col.field == "num33")
                    {
                        item.num33 = ratio;
                    }
                    else if (col.field == "num34")
                    {
                        item.num34 = ratio;
                    }
                    else if (col.field == "num35")
                    {
                        item.num35 = ratio;
                    }
                }
            }

            return item;
        }

        private List<DetailColumn> InitDetailColumns()
        {
            var columns = new List<DetailColumn>
            {
                new DetailColumn() { type = "seq", title = "序号", @fixed = "left", width = "50" },
                new DetailColumn() { field = "DEPT", title = "事业群", @fixed = "left" },
                new DetailColumn() { field = "CATEGORY", title = "一级分类", @fixed = "left" },
                new DetailColumn() { field = "SUB_CATEGORY", title = "二级分类", @fixed = "left" },
                new DetailColumn() { field = "CHILD_CATEGORY", title = "三级分类", @fixed = "left" },
                new DetailColumn() { field = "CATEGORY_MANAGER_CODE", title = "大类负责人", @fixed = "left" },
                new DetailColumn() { field = "CATEGORY_MANAGER", title = "负责人名称" },
                new DetailColumn() { field = "GMV", title = "去年总消耗金额" },
                new DetailColumn() { field = "CATEGORY_RATIO", title = "大类占比%" },
                new DetailColumn() { field = "TOTAL_RATIO", title = "事业群占比%" },
                new DetailColumn() { field = "pre_num3", title = "前3月" },
                new DetailColumn() { field = "pre_num2", title = "前2月" },
                new DetailColumn() { field = "pre_num1", title = "前1月" }
            };

            return columns;
        }

        private List<string> InitHeaderColumns()
        {
            var columns = new List<string>
            {
                "序号",
                "流程编号",
                "申请人",
                "申请日期",
                "原材料/包材",
                "维护类型",
                "数据分类",
                "审批状态",
                "审批人",
                "备注"
            };

            return columns;
        }

        /// <summary>
        /// 把DateTime转成 202308/2023Q3/2023H2
        /// </summary>
        /// <returns></returns>
        private string ConvertDateTimeToCode(string currentTime, DateTime time)
        {
            if (currentTime.Contains("Q"))
            {
                return $"{time:yyyy}Q{(time.Month / 3) + (time.Month % 3 > 0 ? 1 : 0)}";
            }
            else if (currentTime.Contains("H"))
            {
                return $"{time:yyyy}H{(time.Month < 7 ? 1 : 2)}";
            }
            else
            {
                return time.ToString("yyyyMMdd");
            }
        }

        /// <summary>
        /// 获取今年还剩几个月
        /// </summary>
        /// <returns></returns>
        private int GetCurrentYearLeftMonth(string time)
        {
            return 12 - ConvertDateTimeForTimeStr(time).Month;
        }

        private string GetNextYearFirstSeasonTimeStr(string time)
        {
            var dateTime = ConvertDateTimeForTimeStr(time);
            var nextYearFirstSeason = DateTime.Parse(dateTime.AddYears(1).ToString("yyyy-01-01"));
            return GetCurrentNextTime(TimeTypeEnum.Season, nextYearFirstSeason).Item1;
        }

        /// <summary>
        /// 根据202308/2023Q3/2023H2获取后续时间，并返回一个元组，第一个元素是202309/2023Q4/2024H1，第二个元素是202309/2023 4季度/2024 上半年
        /// </summary>
        /// <param name="dateStr"></param>
        /// <param name="nextNum"></param>
        /// <returns></returns>
        private Tuple<string, string> GetCurrentNextTimeForTimeStr(string dateStr, int nextNum = 0)
        {
            var time = ConvertDateTimeForTimeStr(dateStr);
            if (dateStr.ToUpper().Contains("Q"))
            {
                // 季度
                return GetCurrentNextTime(TimeTypeEnum.Season, time, nextNum);
            }
            else if (dateStr.ToUpper().Contains("H"))
            {
                // 半年
                return GetCurrentNextTime(TimeTypeEnum.HalfYear, time, nextNum);
            }
            else
            {
                // 月份
                return GetCurrentNextTime(TimeTypeEnum.Month, time, nextNum);
            }
        }

        /// <summary>
        /// 把字符串202308/2023Q3/2023H2转成年月yyyyMM
        /// </summary>
        /// <param name="dateStr"></param>
        /// <returns></returns>
        private DateTime ConvertDateTimeForTimeStr(string dateStr)
        {
            if (dateStr.ToUpper().Contains("Q"))
            {
                return DateTime.ParseExact(
                    $"{dateStr.ToUpper().Split('Q')[0]}{string.Format("{0:00}", dateStr.ToUpper().Split('Q')[1].ObjToInt() * 3)}", "yyyyMM", null);
            }
            else if (dateStr.ToUpper().Contains("H"))
            {
                return DateTime.ParseExact(
                    $"{dateStr.ToUpper().Split('H')[0]}{string.Format("{0:00}", dateStr.ToUpper().Split('H')[1].ObjToInt() * 6)}", "yyyyMM", null);
            }
            else
            {
                return DateTime.ParseExact(dateStr, "yyyyMM", null);
            }
        }

        /// <summary>
        /// 获取当前时间下nextNum个时间，返回（时间代码202308/2023Q3/2023H2，时间名称202308%/2023 3季度%/2023 下半年%）
        /// </summary>
        /// <param name="timeType"></param>
        /// <param name="currentTime"></param>
        /// <param name="nextNum"></param>
        /// <returns></returns>
        private Tuple<string, string> GetCurrentNextTime(TimeTypeEnum timeType, DateTime currentTime, int nextNum = 0)
        {
            var res = new Tuple<string, string>(string.Empty, string.Empty);
            switch (timeType)
            {
                case TimeTypeEnum.Month:
                case TimeTypeEnum.HalfYearOnMonth:
                case TimeTypeEnum.YearOnMonth:
                    res = Tuple.Create(currentTime.AddMonths(nextNum).ToString("yyyyMM"), currentTime.AddMonths(nextNum).ToString("yyyyMM") + "%");
                    break;
                case TimeTypeEnum.Season:
                case TimeTypeEnum.ThreeYearOnSeason:
                    {
                        var now = currentTime.AddMonths(nextNum * 3);
                        res = Tuple.Create(
                            $"{now:yyyy}Q{(now.Month / 3) + (now.Month % 3 > 0 ? 1 : 0)}",
                            $"{now:yyyy} {(now.Month / 3) + (now.Month % 3 > 0 ? 1 : 0)}季度%");
                        break;
                    }

                case TimeTypeEnum.HalfYear:
                    {
                        var now = currentTime.AddMonths(nextNum * 6);
                        res = Tuple.Create(
                            $"{now:yyyy}H{(now.Month < 7 ? 1 : 2)}",
                            $"{now:yyyy}{(now.Month < 7 ? " 上半年%" : " 下半年%")}");
                        break;
                    }

                default:
                    break;
            }

            return res;
        }

        /// <summary>
        /// 把202308/2023Q3/2023H2转成开始日期和结束日期
        /// </summary>
        /// <param name="dateStr"></param>
        /// <returns></returns>
        private Tuple<DateTime?, DateTime?> ConvertCurrentTimeToStartEnd(string dateStr)
        {
            DateTime? start = null;
            DateTime? end = null;
            try
            {
                string timeFmt = "yyyy-MM-dd HH:mm:ss";
                if (dateStr.ToUpper().Contains("Q"))
                {
                    var times = dateStr.ToUpper().Split('Q');
                    if (times.Length == 2)
                    {
                        if (times[1].ObjToInt().Equals(1))
                        {
                            start = DateTime.ParseExact($"{times[0]}-01-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(3).AddSeconds(-1);
                        }
                        else if (times[1].ObjToInt().Equals(2))
                        {
                            start = DateTime.ParseExact($"{times[0]}-04-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(3).AddSeconds(-1);
                        }
                        else if (times[1].ObjToInt().Equals(3))
                        {
                            start = DateTime.ParseExact($"{times[0]}-07-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(3).AddSeconds(-1);
                        }
                        else if (times[1].ObjToInt().Equals(4))
                        {
                            start = DateTime.ParseExact($"{times[0]}-10-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(3).AddSeconds(-1);
                        }
                    }
                }
                else if (dateStr.ToUpper().Contains("H"))
                {
                    var times = dateStr.ToUpper().Split('H');
                    if (times.Length == 2)
                    {
                        if (times[1].ObjToInt().Equals(1))
                        {
                            start = DateTime.ParseExact($"{times[0]}-01-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(6).AddSeconds(-1);
                        }
                        else if (times[1].ObjToInt().Equals(2))
                        {
                            start = DateTime.ParseExact($"{times[0]}-07-01 00:00:00", timeFmt, null);
                            end = start.Value.AddMonths(6).AddSeconds(-1);
                        }
                    }
                }
                else
                {
                    start = DateTime.ParseExact(dateStr + "01", "yyyyMMdd", null);
                    end = start.Value.AddMonths(1).AddSeconds(-1);
                }
            }
            catch (Exception ex)
            {
                _logger.Error(ex.Message);
            }

            return Tuple.Create(start, end);
        }

        /// <summary>
        /// 修复后期业务扩展，新增字段，导致的表数据不完善
        /// </summary>
        /// <returns></returns>
        public void FixData()
        {
            var headers = _db.Queryable<YCLWLJGZSYCHeader>().ToList();
            var details = _db.Queryable<YCLWLJGZSYCDetail>().ToList();
            var childDatas = _db.Queryable<YCLWLJGZSYCChildData>().ToList();
            //var timeTypes = _db.Queryable<YCLWLJGZSYCTimeType>().ToList();
            foreach(var header in headers)
            {
                var detailList = details.Where(x => x.InstanceNumber == header.InstanceNumber).ToList();
                var childDataList = childDatas.Where(x => x.InstanceNumber == header.InstanceNumber).ToList();
                var timeTypeList = childDatas.Where(x => x.InstanceNumber == header.InstanceNumber).ToList();
                if (header.MaintenanceType == (int)TimeTypeEnum.ThreeYearOnSeason)
                {
                    foreach (var childData in childDataList)
                    {
                        //更新MaintenanceType
                        if (childData.MaintenanceType == 0)
                        {
                            childData.MaintenanceType = childData.MONTH_ID.Contains("Q") ? (int)TimeTypeEnum.ThreeYearOnSeason : (int)TimeTypeEnum.YearOnMonth;
                        }
                        //更新pre_num3,pre_num2,pre_num1
                        var detail = detailList.FirstOrDefault(x => x.DEPT == childData.DEPT &&
                            x.CATEGORY_MANAGER_CODE == childData.CATEGORY_MANAGER_CODE &&
                            x.CATEGORY_ID == childData.CATEGORY_ID && x.SUB_CATEGORY_ID == childData.SUB_CATEGORY_ID &&
                            x.CHILD_CATEGORY_ID == childData.CHILD_CATEGORY_ID);
                        if (detail != null)
                        {
                            if (!childData.pre_num3.HasValue)
                            {
                                childData.pre_num3 = detail.pre_num3;
                            }
                            if (!childData.pre_num2.HasValue)
                            {
                                childData.pre_num2 = detail.pre_num2;
                            }
                            if (!childData.pre_num1.HasValue)
                            {
                                childData.pre_num1 = detail.pre_num1;
                            }
                        }

                        //decimal? currentNum = null;
                        //更新current_num
                        //if (childDataList.Any(x => !x.MONTH_ID.Contains("Q")))
                        //{
                        //    currentNum = childDataList.Where(x => 
                        //        !x.MONTH_ID.Contains("Q") &&
                        //        x.DEPT == childData.DEPT &&
                        //        x.CATEGORY_MANAGER_CODE == childData.CATEGORY_MANAGER_CODE &&
                        //        x.CATEGORY_ID == childData.CATEGORY_ID && 
                        //        x.SUB_CATEGORY_ID == childData.SUB_CATEGORY_ID &&
                        //        x.CHILD_CATEGORY_ID == childData.CHILD_CATEGORY_ID)
                        //        .OrderBy(x => x.MONTH_ID).FirstOrDefault()?.RATIO;
                        //}
                        //else
                        //{
                        //    currentNum = childDataList.Where(x =>
                        //        x.DEPT == childData.DEPT &&
                        //        x.CATEGORY_MANAGER_CODE == childData.CATEGORY_MANAGER_CODE &&
                        //        x.CATEGORY_ID == childData.CATEGORY_ID &&
                        //        x.SUB_CATEGORY_ID == childData.SUB_CATEGORY_ID &&
                        //        x.CHILD_CATEGORY_ID == childData.CHILD_CATEGORY_ID &&
                        //        x.MONTH_ID == header.CurrentTime).FirstOrDefault()?.RATIO;
                        //}
                        //childData.current_num = currentNum;
                        _db.Updateable(childData).ExecuteCommand();
                    }
                }
                else
                {
                    foreach (var childData in childDataList)
                    {
                        //更新MaintenanceType
                        if (childData.MaintenanceType == 0)
                        {
                            childData.MaintenanceType = header.MaintenanceType;
                        }
                        //更新pre_num3,pre_num2,pre_num1
                        var detail = detailList.FirstOrDefault(x => x.DEPT == childData.DEPT &&
                            x.CATEGORY_MANAGER_CODE == childData.CATEGORY_MANAGER_CODE &&
                            x.CATEGORY_ID == childData.CATEGORY_ID && x.SUB_CATEGORY_ID == childData.SUB_CATEGORY_ID &&
                            x.CHILD_CATEGORY_ID == childData.CHILD_CATEGORY_ID);
                        if (detail != null)
                        {
                            if (!childData.pre_num3.HasValue)
                            {
                                childData.pre_num3 = detail.pre_num3;
                            }
                            if (!childData.pre_num2.HasValue)
                            {
                                childData.pre_num2 = detail.pre_num2;
                            }
                            if (!childData.pre_num1.HasValue)
                            {
                                childData.pre_num1 = detail.pre_num1;
                            }
                        }
                        //childData.current_num = childDataList.Where(x =>
                        //    x.DEPT == childData.DEPT &&
                        //    x.CATEGORY_MANAGER_CODE == childData.CATEGORY_MANAGER_CODE &&
                        //    x.CATEGORY_ID == childData.CATEGORY_ID &&
                        //    x.SUB_CATEGORY_ID == childData.SUB_CATEGORY_ID &&
                        //    x.CHILD_CATEGORY_ID == childData.CHILD_CATEGORY_ID &&
                        //    x.MONTH_ID == header.CurrentTime).FirstOrDefault()?.RATIO;
                        _db.Updateable(childData).ExecuteCommand();
                    }
                }
            }
        }
    }
}
